package uk.ac.cam.echo.server.integrationtests;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;

import junit.framework.Assert;

import org.junit.Test;

import uk.ac.cam.echo.messages.CheckInMessage;
import uk.ac.cam.echo.messages.SecurityMessage;
import uk.ac.cam.echo.server.Database;

/** 
 * @author Tom Smith
 */
public class TestCheckInTable extends TestDatabase {

	@Test
	/**
	 * Valid User checking into valid stage NOW.
	 */
	public void checkInNow() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Fred','Red','Guest','46463');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblCheckIn + " " +
					"VALUES('1', 1, '" + new Timestamp(Calendar.getInstance().getTime().getTime())+ "');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test(expected=SQLException.class)
	/**
	 * Valid User checking into valid stage but in the future.
	 */
	public void futureCheckIn() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Fred','Red','Guest','46463');");
		sqlStatement.execute("INSERT INTO " +  Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
		// Date in future.
		sqlStatement.execute("INSERT INTO " + Database.tblCheckIn + " VALUES('1', 1, '3000-01-01 00:00:00');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Time in the future entered.");
	}

	@Test
	/**
	 * Database returns check-ins for this user.
	 */
	public void getCheckIns() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Fred','Red','Guest','46463');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblCheckIn + " " +
					"VALUES('1', 1, '2007-05-01 03:35:18');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			SecurityMessage sec = super.m_db.getSecurityDetails("1");

			List<CheckInMessage> check = sec.getCheckIns();

			Assert.assertEquals(1, check.size());

			CheckInMessage checkIn = check.get(0);

			Assert.assertEquals("Main Stage", checkIn.getAttractionName());

			// Create a calendar to compare to.
			Calendar cal = Calendar.getInstance();
			// N.B. Calendar starts month from 0, while SQL starts from 1.
			cal.set(2007, 04, 01, 03, 35, 18);
			cal.set(Calendar.MILLISECOND, 0);

			Assert.assertEquals(cal.getTimeInMillis(), checkIn.getCheckInTime());

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test(expected=SQLException.class)
	/**
	 * Invalid stage checked in to.
	 */
	public void invalidStage() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Fred','Red','Guest','46463');");
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
		sqlStatement.execute("INSERT INTO " + Database.tblCheckIn + " " +
				"VALUES('1', 2, '2007-05-01 03:35:18');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Invalid location checked into.");
	}

	@Test(expected=SQLException.class)
	/**
	 * Invalid user checking in.
	 */
	public void invalidUser() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Fred','Red','Guest','46463');");
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
		sqlStatement.execute("INSERT INTO " + Database.tblCheckIn + " " +
				"VALUES('2', 1, '2007-05-01 03:35:18');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Invalid user checking in.");
	}

	@Test
	/**
	 * Multiple check ins at different times.
	 */
	public void multipleCheckIn() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Fred','Red','Guest','46463');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(AttractionID, AttractionName, AttractionType)" +
					" VALUES(1, 'Main Stage', 'Stage');");

			for (int i = 0; i < 60; i++) {
				sqlStatement.execute("INSERT INTO " + Database.tblCheckIn + " " +
						"VALUES('1', 1, '2007-06-10 20:" + (i < 10 ? "0" : "") + i + ":00');");
			}
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test
	/**
	 * Valid User checking into valid Stage.
	 */
	public void validUserCheckIn() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Fred','Red','Guest','46463');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblCheckIn + " " +
					"VALUES('1', 1, '2007-05-01 03:35:18');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
}
